const { pool } = require('../config/db.js')

class Chat {
  static async findSessionsByUserId(userId) {
    const [chats] = await pool.query(
      `SELECT c.*,
        (SELECT COUNT(*) FROM messages WHERE chat_id = c.id) as message_count,
        (SELECT content FROM messages WHERE chat_id = c.id ORDER BY created_at ASC LIMIT 1) as first_message
       FROM chats c
       WHERE user_id = ?
       ORDER BY updated_at DESC`,
      [userId]
    )
    return chats
  }

  static async findSessionById(sessionId, userId) {
    const [chats] = await pool.query('SELECT * FROM chats WHERE id = ? AND user_id = ?', [
      sessionId,
      userId
    ])

    if (chats.length === 0) return null

    const [messages] = await pool.query(
      'SELECT * FROM messages WHERE chat_id = ? AND session_id = ? ORDER BY created_at ASC',
      [sessionId, sessionId]
    )

    return {
      ...chats[0],
      messages
    }
  }

  static async createSession(userId, title) {
    const [result] = await pool.query('INSERT INTO chats (user_id, title) VALUES (?, ?)', [
      userId,
      title
    ])
    return result.insertId
  }

  static async addMessage(chatId, role, content, sessionId = 0) {
    const [result] = await pool.query(
      'INSERT INTO messages (chat_id, role, content, session_id) VALUES (?, ?, ?, ?)',
      [chatId, role, content, sessionId]
    )

    await pool.query('UPDATE chats SET updated_at = CURRENT_TIMESTAMP WHERE id = ?', [chatId])

    return result.insertId
  }

  static async findConversationsByUserId(userId) {
    try {
      // 1. 获取用户的所有聊天会话
      const [chats] = await pool.query(
        `SELECT c.*
         FROM chats c
         WHERE user_id = ?
         ORDER BY updated_at DESC`,
        [userId]
      )

      // 2. 对于每个会话，获取其所有消息（根据session_id匹配）
      const result = []
      for (const chat of chats) {
        const [messages] = await pool.query(
          `SELECT * FROM messages
           WHERE chat_id = ? AND session_id = ?
           ORDER BY created_at ASC`,
          [chat.id, chat.id]
        )

        result.push({
          ...chat,
          messages: messages || []
        })
      }

      return result
    } catch (error) {
      console.error('获取会话列表出错:', error)
      throw error
    }
  }

  // 迁移现有消息数据，将session_id更新为对应的chat_id
  static async migrateMessagesSessionId() {
    try {
      // 首先检查是否有需要迁移的消息
      const [rows] = await pool.query(
        `SELECT COUNT(*) AS count FROM messages WHERE session_id = 0 OR session_id IS NULL`
      )

      const count = rows[0].count

      if (count > 0) {
        // 更新所有session_id为0或NULL的消息，设置为其对应的chat_id
        await pool.query(
          `UPDATE messages SET session_id = chat_id WHERE session_id = 0 OR session_id IS NULL`
        )
      } else {
        console.log('没有需要迁移的消息')
      }

      return true
    } catch (err) {
      console.error('消息session_id迁移失败:', err)
      return false
    }
  }

  // 删除单个会话
  static async deleteConversation(chatId, userId) {
    try {
      // 开始事务
      const connection = await pool.getConnection();
      await connection.beginTransaction();

      try {
        // 确认聊天属于该用户
        const [chats] = await connection.query(
          'SELECT * FROM chats WHERE id = ? AND user_id = ?',
          [chatId, userId]
        );

        if (chats.length === 0) {
          await connection.rollback();
          connection.release();
          return { success: false, message: '会话不存在或无权删除' };
        }

        // 删除会话相关的所有消息
        await connection.query(
          'DELETE FROM messages WHERE chat_id = ?',
          [chatId]
        );

        // 删除会话本身
        await connection.query(
          'DELETE FROM chats WHERE id = ?',
          [chatId]
        );

        // 提交事务
        await connection.commit();
        connection.release();

        return { success: true, message: '会话删除成功' };
      } catch (error) {
        // 发生错误，回滚事务
        await connection.rollback();
        connection.release();
        console.error('删除会话失败:', error);
        throw error;
      }
    } catch (error) {
      console.error('删除会话出错:', error);
      throw error;
    }
  }

  // 批量删除会话
  static async deleteMultipleConversations(chatIds, userId) {
    if (!chatIds || !Array.isArray(chatIds) || chatIds.length === 0) {
      return { success: false, message: '无效的会话ID列表' };
    }

    try {
      // 开始事务
      const connection = await pool.getConnection();
      await connection.beginTransaction();

      try {
        // 确认所有聊天都属于该用户
        const placeholders = chatIds.map(() => '?').join(',');
        const [chats] = await connection.query(
          `SELECT id FROM chats WHERE id IN (${placeholders}) AND user_id = ?`,
          [...chatIds, userId]
        );

        // 获取有权限删除的会话ID
        const validChatIds = chats.map(chat => chat.id);

        if (validChatIds.length === 0) {
          await connection.rollback();
          connection.release();
          return { success: false, message: '没有可删除的会话' };
        }

        // 构建占位符
        const validPlaceholders = validChatIds.map(() => '?').join(',');

        // 删除这些会话的所有消息
        await connection.query(
          `DELETE FROM messages WHERE chat_id IN (${validPlaceholders})`,
          validChatIds
        );

        // 删除会话本身
        await connection.query(
          `DELETE FROM chats WHERE id IN (${validPlaceholders})`,
          validChatIds
        );

        // 提交事务
        await connection.commit();
        connection.release();

        return {
          success: true,
          message: '批量删除成功',
          deleted: validChatIds,
          notDeleted: chatIds.filter(id => !validChatIds.includes(id))
        };
      } catch (error) {
        // 发生错误，回滚事务
        await connection.rollback();
        connection.release();
        console.error('批量删除会话失败:', error);
        throw error;
      }
    } catch (error) {
      console.error('批量删除会话出错:', error);
      throw error;
    }
  }
}

module.exports = Chat
